﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using SAServices.Model;

namespace SAServices.DAL
{
    internal class CategoryDAL
    {
        internal static List<Guid> GetCatIdsByParentId(Model.ConnectionConext connCtx, Guid parentId)
        {
            string sql = String.Format("SELECT ID FROM Categories WHERE ParentID = '{0}' ORDER BY Idx;", parentId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            List<Guid> retList = new List<Guid>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string ret = Convert.ToString(dt.Rows[i]["ID"]);
                Guid tempId = new Guid(ret);

                retList.Add(tempId);
            }

            return retList;
        }

        internal static Model.CategoryInfo GetCateInfoById(Model.ConnectionConext connCtx, Guid tempCateId)
        {
            CategoryInfo retCateInfo = new CategoryInfo();

            string sql = String.Format("SELECT * FROM Categories WHERE ID = '{0}'", tempCateId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count >0)
            {
                retCateInfo.Id = new Guid(Convert.ToString(dt.Rows[0]["ID"]));
                retCateInfo.Name = Convert.ToString(dt.Rows[0]["Name"]);
                retCateInfo.ParentId = new Guid(Convert.ToString(dt.Rows[0]["ParentID"]));
                retCateInfo.Idx = Convert.ToInt32(dt.Rows[0]["Idx"]);
            }

            return retCateInfo;
        }

        internal static bool UpdateCategory(Model.ConnectionConext connCtx, Model.CategoryInfo updateCategoryInfo)
        {
            string sql = String.Format("UPDATE Categories SET Name = {0}, ParentID = {1}, Idx = {2} WHERE ID = {3};",
                "'" + updateCategoryInfo.Name + "'",
                "'" + updateCategoryInfo.ParentId + "'",
                updateCategoryInfo.Idx,
                "'" + updateCategoryInfo.Id + "'");

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static bool AddCategory(Model.ConnectionConext connCtx, Model.CategoryInfo newCategoryInfo)
        {
            string sql = String.Format("INSERT INTO Categories(ID, Name, ParentID, Idx) Values({0}, {1}, {2}, {3});",
                "'" + newCategoryInfo.Id + "'",
                "'" + newCategoryInfo.Name + "'",
                 "'" + newCategoryInfo.ParentId + "'",
                newCategoryInfo.Idx);

            return GenDAL.ExecuteNonQuery(connCtx, sql);

        }

        internal static bool DeleteCategory(Model.ConnectionConext connCtx, Guid cateId)
        {
            string sql = String.Format("DELETE FROM Categories WHERE ID = '{0}'", cateId);

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }
    }
}
